package com.duleme.dao;

import com.duleme.common.DBUtils;
import com.duleme.entity.Shop;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShopDao {
    //插入店铺
    public boolean insertShop(Shop shop){
        String sql="INSERT INTO shop (shopName,createTime,ownerId,totalSell,count,shopAvatar) VALUES(?,?,?,?,?,?)";
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,shop.getShopName());
            ps.setDate(2,new java.sql.Date(shop.getCreateTime().getTime()));
            ps.setInt(3,shop.getOwnerId());
            ps.setInt(4,shop.getTotalSell());
            ps.setInt(5,shop.getCount());
            ps.setString(6,shop.getShopAvatar());
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }

    public Shop findShopByName(String name){
        String sql="SELECT * FROM shop WHERE shopName=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                Shop shop=new Shop();
                shop.setCount(rs.getInt("count"));
                shop.setOwnerId(rs.getInt("ownerId"));
                shop.setCreateTime(rs.getDate("createTime"));
                shop.setShopAvatar(rs.getString("shopAvatar"));
                shop.setShopId(rs.getInt("shopId"));
                shop.setShopName(rs.getString("shopName"));
                shop.setTotalSell(rs.getInt("totalSell"));
                DBUtils.close(connection,ps,rs);
                return shop;
            }
            DBUtils.close(connection,ps,rs);
            return null;
        }catch (Exception e){
            e.printStackTrace();
            return  null;
        }
    }

    public int findShopIdByOwnerId(int id){
        String sql="SELECT shopId FROM shop WHERE ownerId=?";
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                int shopid=rs.getInt("shopId");
                DBUtils.close(connection,ps,rs);
                return shopid;
            }
            DBUtils.close(connection,ps,rs);
            return -1;
        }catch (Exception e){
            e.getStackTrace();
            return -1;
        }
    }

    //查询所有店铺
    public List<Shop> findAllShops(){
        String sql="SELECT * FROM shop";
        try {
            List<Shop> shops=new ArrayList<>();
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                Shop shop=new Shop();
                shop.setShopId(rs.getInt("shopId"));
                shop.setOwnerId(rs.getInt("ownerId"));
                shop.setCreateTime(rs.getDate("createTime"));
                shop.setShopAvatar(rs.getString("shopAvatar"));
                shop.setTotalSell(rs.getInt("totalSell"));
                shop.setShopName(rs.getString("shopName"));
                shop.setCount(rs.getInt("count"));
                shops.add(shop);
            }
            DBUtils.close(connection,ps,rs);
            return shops;
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }

    //根据id删除店铺
    public boolean deleteShopById(int id){
        String sql="DELETE FROM shop WHERE shopId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }

    //修改店铺头像和店铺名
    public boolean update(String avatar,String shopName,int shopId){
        String sql="UPDATE shop SET shopAvatar=?,shopName=? WHERE shopId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,avatar);
            ps.setString(2,shopName);
            ps.setInt(3,shopId);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return false;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    //删除对应店铺和店铺内的所有书
    public boolean deepDeleteShopById(int shopId) throws SQLException {
        String sql1="DELETE FROM shop WHERE shopId=?";
        String sql2="DELETE FROM book WHERE shopId=?";
        Connection connection=DBUtils.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement ps1=connection.prepareStatement(sql1);
        PreparedStatement ps2=connection.prepareStatement(sql2);
        try {
            ps1.setInt(1,shopId);
            ps2.setInt(1,shopId);
            ps1.executeUpdate();
            ps2.executeUpdate();
            connection.commit();
            return true;
        }catch (Exception e){
            e.printStackTrace();
            connection.rollback();
            return false;
        }finally {
            if (connection!=null)connection.close();
            if (ps1!=null)ps1.close();
            if (ps2!=null)ps2.close();
        }
    }

    public Shop findShopById(int id){
        String sql="SELECT * FROM shop WHERE shopId=?";
        try{
            Connection connection= DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                Shop shop=new Shop();
                shop.setShopId(rs.getInt("shopId"));
                shop.setOwnerId(rs.getInt("ownerId"));
                shop.setCreateTime(rs.getDate("createTime"));
                shop.setShopAvatar(rs.getString("shopAvatar"));
                shop.setTotalSell(rs.getInt("totalSell"));
                shop.setShopName(rs.getString("shopName"));
                shop.setCount(rs.getInt("count"));
                return shop;
            }
            DBUtils.close(connection,ps,rs);
            return  null;
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }

    public static void main(String[] args){
//        List<Goods> l=new ShopDao().findAllBooksInShop("新华书店");
//        System.out.println(l.get(0).getBookName());
        ShopDao dao=new ShopDao();
        dao.update("aa","诺克萨斯",1);


    }
}
